# -*- coding:utf-8 -*-

from typing import List
from datetime import datetime, time
from module_admin.entity.do.role_do import SysRoleDept
from sqlalchemy import and_, delete, desc, func, or_, select, update
from sqlalchemy.ext.asyncio import AsyncSession
from module_gen.constants.gen_constants import GenConstants
from module_product.entity.do.bg_order_do import BgOrder
from module_product.entity.vo.bg_order_vo import BgOrderPageModel, BgOrderModel
from utils.page_util import PageUtil, PageResponseModel
from utils.common_util import CamelCaseUtil


class BgOrderDao:

    @classmethod
    async def get_by_id(cls, db: AsyncSession, bg_order_id: int) -> BgOrder:
        """根据主键获取单条记录"""
        bg_order = (((await db.execute(
                            select(BgOrder)
                            .where(BgOrder.id == bg_order_id)))
                       .scalars())
                       .first())
        return bg_order

    @classmethod
    async def get_by_product_pay(cls, db: AsyncSession, real_pay: int) -> list[BgOrder]:
        """根据主键获取单条记录"""
        bg_orders = (((await db.execute(
            select(BgOrder)
            .where(BgOrder.product_pay == real_pay,
                   BgOrder.status == '0')
            .limit(3).order_by(desc(BgOrder.create_time))))
                     .scalars())
                    .all())
        return list(bg_orders)

    """
    查询
    """
    @classmethod
    async def get_bg_order_list(cls, db: AsyncSession,
                             query_object: BgOrderPageModel,
                             data_scope_sql: str = None,
                             is_page: bool = False) -> [list | PageResponseModel]:

        query = (
            select(BgOrder)
            .where(
                BgOrder.create_time.between(query_object.begin_create_time, query_object.end_create_time) if query_object.create_time else True,
                BgOrder.product_id == query_object.product_id if query_object.product_id else True,
                BgOrder.product_month == query_object.product_month if query_object.product_month else True,
                BgOrder.product_name.like(f"%{query_object.product_name}%") if query_object.product_name else True,
                BgOrder.user_id == query_object.user_id if query_object.user_id else True,
                BgOrder.status.in_(['1', '2']),
                BgOrder.del_flag == '0',
                eval(data_scope_sql) if data_scope_sql else True,
            )
            .order_by(desc(BgOrder.create_time))
            .distinct()
        )
        bg_order_list = await PageUtil.paginate(db, query, query_object.page_num, query_object.page_size, is_page)
        return bg_order_list


    @classmethod
    async def add_bg_order(cls, db: AsyncSession, add_model: BgOrderModel, auto_commit: bool = True) -> BgOrderModel:
        """
        增加
        """
        bg_order = BgOrder(**add_model.model_dump(exclude_unset=True, ))
        db.add(bg_order)
        await db.flush()
        bg_order_model = BgOrderModel(**CamelCaseUtil.transform_result(bg_order))
        if auto_commit:
            await db.commit()
        return bg_order_model

    @classmethod
    async def edit_bg_order(cls, db: AsyncSession, edit_model: BgOrderModel, auto_commit: bool = True) -> BgOrder:
        """
        修改
        """
        edit_dict_data = edit_model.model_dump(exclude_unset=True, exclude={ *GenConstants.DAO_COLUMN_NOT_EDIT })
        await db.execute(update(BgOrder), [edit_dict_data])
        await db.flush()
        if auto_commit:
            await db.commit()
        return await cls.get_by_id(db, edit_model.id)

    @classmethod
    async def del_bg_order(cls, db: AsyncSession, bg_order_ids: List[str], soft_del: bool = True, auto_commit: bool = True):
        """
        删除
        """
        if soft_del:
            await db.execute(update(BgOrder).where(BgOrder.id.in_(bg_order_ids)).values(del_flag='2'))
        else:
            await db.execute(delete(BgOrder).where(BgOrder.id.in_(bg_order_ids)))
        await db.flush()
        if auto_commit:
            await db.commit()

    @classmethod
    async def get_order_statistics(cls, db: AsyncSession) -> dict:
        """
        获取订单统计信息
        """
        # 总订单数
        total_query = select(func.count(BgOrder.id)).where(BgOrder.del_flag == '0')
        total_result = await db.execute(total_query)
        total_orders = total_result.scalar()

        # 已支付订单数
        paid_query = select(func.count(BgOrder.id)).where(
            BgOrder.status == '1',
            BgOrder.del_flag == '0'
        )
        paid_result = await db.execute(paid_query)
        paid_orders = paid_result.scalar()

        # 未支付订单数
        unpaid_query = select(func.count(BgOrder.id)).where(
            BgOrder.status == '0',
            BgOrder.del_flag == '0'
        )
        unpaid_result = await db.execute(unpaid_query)
        unpaid_orders = unpaid_result.scalar()

        # 已退款订单数
        refunded_query = select(func.count(BgOrder.id)).where(
            BgOrder.status == '2',
            BgOrder.del_flag == '0'
        )
        refunded_result = await db.execute(refunded_query)
        refunded_orders = refunded_result.scalar()

        # 总销售额
        revenue_query = select(func.sum(BgOrder.product_pay)).where(
            BgOrder.status == '1',
            BgOrder.del_flag == '0'
        )
        revenue_result = await db.execute(revenue_query)
        total_revenue = revenue_result.scalar() or 0

        return {
            'totalOrders': total_orders,
            'paidOrders': paid_orders,
            'unpaidOrders': unpaid_orders,
            'refundedOrders': refunded_orders,
            'totalRevenue': total_revenue
        }